﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2012/2/8 20:16:48
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sector7.Restaurant.EntityType;
using Sector7.Framework.Core;
using System.Data.SqlClient;
using Sector7.Framework.Utility;
using Sector7.Restaurant.DAO.Utility;
using Sector7.Framework.DB;

namespace Sector7.Restaurant.DAO.OleDB
{
    /// <summary>
    /// 餐桌数据访问对象
    /// </summary>
    public class TableDAO : ITableDAO
    {
        #region Public functions
        public int Create(RS_Table table)
        {
            using (DBHelper opt = new DBHelper())
            {
                string sql = @"
                    INSERT INTO RS_Table
                            (TableNo
                            ,RoomId
                            ,SeatCount
                            ,Status)
                        VALUES
                            (@TableNo
                            ,@RoomId
                            ,@SeatCount
                            ,@Status)
                    SELECT @@IDENTITY";

                return Convert.ToInt32(opt.ExecuteScalar(
                    sql,
                    DBUtil.CreateSqlParameter(table.TableNo, "TableNo"),
                    DBUtil.CreateSqlParameter(table.RoomId, "RoomId"),
                    DBUtil.CreateSqlParameter(table.SeatCount, "SeatCount"),
                    DBUtil.CreateSqlParameter(table.Status, "Status")));
            }
        }

        public List<RS_Table> ReadAll()
        {
            using (DBHelper opt = new DBHelper())
            {
                List<RS_Table> tables = new List<RS_Table>();
                string sql = @"
                    SELECT *
                        FROM RS_Table AS diningTable
                        LEFT JOIN RS_Room AS room
                            ON diningTable.RoomId = room.RoomId
                        ORDER BY diningTable.RoomId, diningTable.TableNo ASC";

                List<SqlParameter> sqlParams = new List<SqlParameter>();

                SqlDataReader reader = opt.ExecuteQuery(sql);

                while (reader.Read())
                {
                    object table = new RS_Table();

                    DBUtil.ReadData(reader, ref table);

                    tables.Add(table as RS_Table);
                }

                return tables;
            }
        }

        public int Update(EntityDelta delta)
        {
            using (DBHelper opt = new DBHelper())
            {
                return opt.ExecuteNonQuery(
                    DBUtil.CreateUpdateSql(delta),
                    DBUtil.CreateUpdateParameters(delta));
            }
        }

        /*******************************************************************************/

        public RS_Table Read(int tableId)
        {
            using (DBHelper opt = new DBHelper())
            {
                object table = null;
                string sql =
                    "SELECT *                                   " +
                    "   FROM RS_Table AS diningTable      " +
                    "   LEFT JOIN RS_Room AS room        " +
                    "       ON diningTable.RoomId = room.RoomId " +
                    "   WHERE                                   " +
                    "       diningTable.TableId = @TableId      "+
                    "   ORDER BY                                "+
                    "       diningTable.RoomId ASC              ";

                SqlDataReader reader = opt.ExecuteQuery(
                    sql,
                    DBUtil.CreateSqlParameter(tableId, "TableId"));

                if (reader.Read())
                {
                    table = new RS_Table();

                    DBUtil.ReadData(reader, ref table);
                }

                return table as RS_Table;
            }
        }

        public List<RS_Table> ReadAll(int roomId)
        {
            return ReadAll(roomId, true);
        }

        public List<RS_Table> ReadAll(int roomId, bool isEqualsRoomId)
        {
            using (DBHelper opt = new DBHelper())
            {
                List<RS_Table> tables = new List<RS_Table>();
                string sql =
                    "SELECT *                                   " +
                    "   FROM RS_Table AS diningTable            " +
                    "   LEFT JOIN RS_Room AS room               " +
                    "       ON diningTable.RoomId = room.RoomId ";

                List<SqlParameter> sqlParams = new List<SqlParameter>();

                if (roomId != -1)
                {
                    sql += string.Format(
                        "   WHERE                   " +
                        "       diningTable.RoomId {0} @RoomId  ",
                        isEqualsRoomId ? "=" : "<>");

                    sqlParams.Add(DBUtil.CreateSqlParameter(roomId, "RoomId"));
                }
                SqlDataReader reader = opt.ExecuteQuery(sql, sqlParams.ToArray());

                while (reader.Read())
                {
                    object table = new RS_Table();

                    DBUtil.ReadData(reader, ref table);

                    tables.Add(table as RS_Table);
                }

                return tables;
            }
        }

        public int Delete(int tableId)
        {
            using (DBHelper opt = new DBHelper())
            {
                string sql =
                    "DELETE FROM RS_Table     " +
                    "   WHERE TableId = @TableId    ";

                return opt.ExecuteNonQuery(
                    sql,
                    DBUtil.CreateSqlParameter(tableId, "TableId"));
            }
        }
        #endregion
    }
}
